<?php

namespace App\Http\Controllers\Admin\ExportReport;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class ExportReportShanXiShiJiZhongShiController extends Controller
{
    private $total_page = 0;
    private static $footerContent = '&C&12第 &P-2 页，共 &N-2 页'; //页脚内容

    public function export(Request $request) {
        set_time_limit(0);
        ini_set('max_execution_time', 0);
        ini_set('memory_limit', -1); //没有内存限制
        // dd(ini_get('memory_limit'));

        $contract_id = $request->id; //批次 id
        //查询当前批次的信息
        $data_batch = DB::table('a_contract')->find($contract_id);
        $batchNum = $data_batch->batchNum;

        $storeFile = 'reports/'.$batchNum.'.xls';
        $disk = Storage::disk('public');
        // dd($data_batch);
        if($disk->exists($storeFile) && $request->newer != 1 && $data_batch->status > 13) {
            // dd($storeFile);
            return $disk->download($storeFile);
        }

        $DY_RiQi = strtotime($data_batch->created_at); //将表示时间和日期的字符串转化为相应的时间戳
        //$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
        $report_num_pre = DB::table('setting')->where('id', '1')->value('report_num_pre'); //报告编号前缀
        $company_name = DB::table('setting')->where('id', '1')->value('name_rep'); //获取检测公司名称

        // https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#setting-the-print-header-and-footer-of-a-worksheet
        //$headerLeft = "&L报告编号：{$report_num_pre}{$data_batch->batchNum}"; //眉头--左
        //$headerCenter = "&C&15&B检 测 报 告"; //眉头--中
        //$headerRight = "&R&12第 &P-3 页，共 &N-3 页"; //眉头--右
        //$headerContent = "{$headerLeft}{$headerCenter}{$headerRight}"; //眉头内容

        $headerLeft = "&L&U国网山西省电力公司电力科学研究院                                                          "; //眉头-必须要有空格，这样才可贯穿横线
        $headerRight = "                             &R&U报告编号：{$report_num_pre}{$data_batch->batchNum}"; //眉头
        $headerContent = "{$headerLeft}{$headerRight}"; //眉头内容
        $headerPageMargins = 0.4; //眉头的页边距

        $footerContent = '&R&12第 &P-3 页，共 &N-3 页'; //页脚内容
        $footerPageMargins = 0.3; //页脚的页边距

        $bottomPageMargins = 0.5; //下边的页边距
        //$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

        //================================================== 封面
        $spreadsheet = $this->exportFengMian($report_num_pre, $data_batch,$headerContent,$headerPageMargins);

        //================================================== 封底
        $this->exportFengDi($spreadsheet,$report_num_pre, $data_batch,$headerContent,$headerPageMargins);

        //================================================== 结论
        $this->exportJieLun($spreadsheet, $contract_id, $DY_RiQi, $data_batch,$headerContent,$headerPageMargins);

        //================================================== 内容页
        $this->exportContent($spreadsheet, $contract_id, $data_batch, $headerContent, $headerPageMargins);

        //===================================================结束页
        $this->exportJieShu($spreadsheet, $headerContent, $headerPageMargins,$contract_id);

        $sample_infos = DB::table('a_sample_info as B')
            ->select('B.*', 'A.tool_name', 'A.voltage', 'A.class')
            ->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')
            ->where('B.contract_id', $contract_id)
            ->orderBy('bar_code')
            ->get();
        //================================================== 样品信息
        $this->exportSampleInfo($spreadsheet, $sample_infos, $bottomPageMargins);

        //================================================== 斗臂车铭牌照片
        $this->exportPhoto($spreadsheet, $sample_infos, 'photo', 'B', '斗臂车铭牌照片');

        //================================================== 不合格照片
        $this->exportPhoto($spreadsheet, $sample_infos, 'non_photo', 'C', '不合格照片');

        // $worksheet = $spreadsheet->getActiveSheet();//获取到激活的表单

        //----------直接在浏览器下载----start
        $file_name = "检测报告".$data_batch->batchNum.".xls"; //输出的文件名称
        // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出Excel 07文件
        header('Content-Type: application/vnd.ms-excel'); //告诉浏览器输出Excel 03版本文件
        header('Content-Disposition: attachment;filename='.urlencode($file_name)); //告诉浏览器输出的文件名称
        header('Cache-Control: max-age=0'); //禁止缓存
        //----------直接在浏览器下载----end

        /* 生成HTML */
        // $writer = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
        // $writer->writeAllSheets();
        // $writer->save("05featuredemo.htm");
        /* 生成HTML */

        $writer = IOFactory::createWriter($spreadsheet, 'Xls');
        $writer->setPreCalculateFormulas(false); //禁用公式预先计算

        //将xls文件保存,将Excel文件保存在服务器的磁盘上
        try{
            $disk->put($storeFile,'');
            $writer->save($disk->path($storeFile));
        }catch(Exception $e){
            $disk->delete($storeFile);
        }

        // return $disk->download($storeFile);
        // $writer->save('write.xls');//将Excel文件保存在服务器的磁盘上
        // dd(11);
        $writer->save('php://output'); //直接在浏览器下载（第一次只能这样下载）

        /* //未测试通过
        // $writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf($spreadsheet);
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Mpdf');
        $writer->setPreCalculateFormulas(false);//禁用公式预先计算
        $writer->save("05featuredemo.pdf"); */

        //为了防止内存泄露，建议用完手动清理
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);

    }

    /* 结束页 */
    private function exportJieShu($spreadsheet, $headerContent, $headerPageMargins,$contract_id){
        $template_file = './storage/ExcelTemplate/结束页.xls';
        if(file_exists($template_file)){
            $spreadsheet_jiesu = IOFactory::load($template_file);
            $worksheet_jiesu = $spreadsheet_jiesu->getSheet(0);
            $worksheet_jiesu->setTitle('结束页');

            $worksheet_jiesu->getHeaderFooter()->setOddHeader($headerContent); //检测公司名称+报告编号前缀+报告编号 Odd
            $worksheet_jiesu->getHeaderFooter()->setEvenHeader($headerContent); //检测公司名称+报告编号前缀+报告编号 Even
            $worksheet_jiesu->getPageMargins()->setHeader($headerPageMargins); //设置眉头的页边距

            $total_page = $this->total_page;

            $worksheet_jiesu->getHeaderFooter()->setOddFooter(self::$footerContent); //页脚  Odd
            $worksheet_jiesu->getHeaderFooter()->setEvenFooter(self::$footerContent); //页脚  Even
            $worksheet_jiesu->getPageMargins()->setFooter(0.3); //设置页脚的页边距
            $worksheet_jiesu->getPageMargins()->setBottom(0.5); //设置下边的页边距

            //斗臂车铭牌
            $doubiche_colume = 'photo';
            $doubiche_data_sample_info = DB::table('a_sample_info')
                ->select($doubiche_colume.' as photo', 'bar_code')
                ->where('contract_id', $contract_id)
                ->where($doubiche_colume, '!=', '')
                ->orderBy('bar_code')
                ->get();
            $doubiche_count = count($doubiche_data_sample_info);
            //不合格
            $buhege_column = 'non_photo';
            $buhege_data_sample_info = DB::table('a_sample_info')
                ->select($buhege_column.' as photo', 'bar_code')
                ->where('contract_id', $contract_id)
                ->where($buhege_column, '!=', '')
                ->orderBy('bar_code')
                ->get();
            $buhege_count = count($buhege_data_sample_info);

            $worksheet_jiesu->getCell('A2')->setValue("附件A:检验样品明细");
            if($doubiche_count && $buhege_count){
                $worksheet_jiesu->getCell('A3')->setValue("附件B:斗臂车铭牌照片");
                $worksheet_jiesu->getCell('A4')->setValue("附件C:不合格样品照片");
            }elseif($doubiche_count){
                $worksheet_jiesu->getCell('A3')->setValue("附件B:斗臂车铭牌照片");
            }elseif($buhege_count){
                $worksheet_jiesu->getCell('A3')->setValue("附件C:不合格样品照片");
            }

            $worksheet_jiesu->getCell('H6')->setValue("第 {$total_page} 页，共 {$total_page} 页");
            $spreadsheet->addExternalSheet($worksheet_jiesu);
            $spreadsheet_jiesu->disconnectWorksheets();
            unset($spreadsheet_jiesu);
        }
    }

    /* 样品信息 */
    private function exportSampleInfo($spreadsheet, $sample_infos, $bottomPageMargins) {
        $template_file = './storage/ExcelTemplate/山西样品信息.xls'; //考虑到兼容Linux，斜杠用“/”
        $exist_sample_info_excel = file_exists($template_file);
        if($exist_sample_info_excel) {
            $spreadsheet_sam_info = IOFactory::load($template_file); //读取模板文件
            $worksheet = $spreadsheet_sam_info->getSheet(0); //按索引获取表单，从 0 开始
            $worksheet->setTitle('样品信息'); //重命名工作表
            $spreadsheet->addExternalSheet($worksheet); //添加外部工作表
            //为了防止内存泄漏，建议用完手动清理
            $spreadsheet_sam_info->disconnectWorksheets();
            unset($spreadsheet_sam_info);

            $worksheet = $spreadsheet->getSheetByName('样品信息'); //按表单名 获取到激活的表单

            // $headerContent = "&L报告编号：{$report_num_pre}{$data_batch->batchNum}"; //眉头内容
            // $worksheet->getHeaderFooter()->setOddHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(样品信息页眉头)  Odd
            // $worksheet->getHeaderFooter()->setEvenHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(样品信息页眉头) Even
            // $worksheet->getPageMargins()->setHeader($headerPageMargins); //设置眉头的页边距

            // $worksheet->getHeaderFooter()->setOddFooter($footerContent); //页脚  Odd
            // $worksheet->getHeaderFooter()->setEvenFooter($footerContent); //页脚  Even
            // $worksheet->getPageMargins()->setFooter($footerPageMargins); //设置页脚的页边距

            $worksheet->getPageMargins()->setBottom($bottomPageMargins); //设置下边的页边距

            $rowIndex = 3; //样品信息的行号
            // dd($sample_infos);
            $sampleCount = count($sample_infos);
            if ($sampleCount > 1) {
                $lastIndex = $sampleCount-1+$rowIndex;
                // for($rowI=$rowIndex; $rowI <= $lastIndex; $rowI++){
                //     $worksheet->getRowDimension($rowI)->setRowHeight(-1);
                // }
                $worksheet->insertNewRowBefore((int)$rowIndex, $sampleCount-1); 
                $highestColumnIndex = Coordinate::columnIndexFromString($worksheet->getHighestDataColumn());
                for ($colIndex=1; $colIndex <= $highestColumnIndex; $colIndex++) { 
                    $col = Coordinate::stringFromColumnIndex($colIndex);
                    $pCellStyle = $worksheet->getStyle($col.$lastIndex);
                    $worksheet->duplicateStyle($pCellStyle,$col.$rowIndex.':'.$col.$lastIndex);
                }
                // dd($rowI,$rowIndex,$lastIndex);
            }
            foreach($sample_infos as $kk => $vv) {
                $worksheet->getCell("A".$rowIndex)->setValue($kk + 1); //序号
                $worksheet->getCell("B".$rowIndex)->setValue($vv->tool_name); //样品信息---样品名称
                // $worksheet->getCell("C" . $rowIndex)->setValue($vv->bar_code); //样品信息---样品编号
                $worksheet->getCell("C".$rowIndex)->setValueExplicit($vv->bar_code, DataType::TYPE_STRING2); //样品信息---样品编号
                $worksheet->getCell("D".$rowIndex)->setValue($vv->self_num); //样品信息---样品自编号
                $worksheet->getCell("E".$rowIndex)->setValue($vv->guige_xinghao); //样品信息---规格型号
                $worksheet->getCell("F".$rowIndex)->setValue($vv->voltage); //样品信息---额定电压
                $worksheet->getCell("G".$rowIndex)->setValue($vv->sample_status); //样品信息---样品状态
                $worksheet->getCell("H".$rowIndex)->setValue($vv->brand); //样品信息---商标
                $worksheet->getCell("I".$rowIndex)->setValue($vv->manufacturer); //样品信息---生产厂家
                $worksheet->getCell("J".$rowIndex)->setValue($vv->production_date); //样品信息---生产日期
                $rowIndex ++;
            }
        }
    }

    /* 不合格照片/斗臂车铭牌照片*/
    private function exportPhoto($spreadsheet, $sample_infos, $column, $fujian, $title) {
        $sample_infos2 = [];
        $template_file = './storage/ExcelTemplate/斗臂车铭牌照片.xls'; //考虑到兼容Linux，斜杠用“/”
        if(file_exists($template_file)) {
            $spreadsheet_common = IOFactory::load($template_file); //读取模板文件
            $worksheet_common = $spreadsheet_common->getActiveSheet(); //按索引获取表单，从 0 开始
            $worksheet_common->setTitle($title); //重命名工作表
            $worksheet_common->getHeaderFooter()->setOddHeader('&L&B附件'.$fujian);;  //页眉
            $worksheet_common->getCell('A1')->setValue($title);
            foreach ($sample_infos as $item) {
                if ($item->$column != '') {
                    $sample_infos2[] = (object)[
                        'photo'=>$item->$column,
                        'bar_code'=>$item->bar_code,
                    ];
                }
            }
            $sample_infos = $sample_infos2;
            // dd($sample_infos);
            //测试数据
            //            $sample_infos = [];
            //            for ($i = 1; $i < 8; $i++) {
            //                $sample_infos[] = (object)['photo' => '/storage/images/sample_info/non/21000700' . $i . '.jpg', 'bar_code' => 123456];
            //            }
            $count = count($sample_infos);
            if($count === 0) {
                return 0;
            }
            $base = 1;
            $towidth = 330;
            if($count < 3) { 
                $towidth = 550;
            }
            $toheight = 0.75*$towidth;
            $rowheight = $count > 2 ? 230 : 330;
            $offsetX = $count > 2 ? 10 : 80;
            foreach($sample_infos as $k => $val) {
                // dd($val);
                $path = substr($val->photo, 1);
                if(!is_file($path) || filesize($path) === 0) {
                    continue;
                }
                $drawing = new Drawing();
                $drawing->setPath($path);
                $drawing->setResizeProportional(false);
                $drawing->setWidth($towidth);
                $drawing->setHeight($toheight);
                $base += 1;
                $row = 'A';
                if($count > 2) {
                    $k%2 === 0 ? $base : $base -= 1;
                    $row = $k%2 === 0 ? 'A' : 'B';
                }
                //                文字格式设置
                $textcell = $row.$base;
                $worksheet_common->getCell($textcell)->setValue('样品编号：'.$val->bar_code);
                //                图片格式设置
                $pos = $row.$base;
                $drawing->setCoordinates($pos);
                $drawing->setOffsetX($offsetX);
                $drawing->setOffsetY(20);
                $drawing->setWorksheet($worksheet_common);
                $worksheet_common->getRowDimension($base)->setRowHeight($rowheight);

            }
            $worksheet_common->getStyle('A2:B'.$base)->getAlignment()->setVertical(Alignment::VERTICAL_TOP);
            //设置内容适应页面
            //            $worksheet_common->getPageSetup()->setFitToPage(true);

            $spreadsheet->addExternalSheet($worksheet_common); //添加外部工作表
            //为了防止内存泄漏，建议用完手动清理
            $spreadsheet_common->disconnectWorksheets();
            unset($spreadsheet_common,$worksheet_common);
        }
    }


    /* 封底 */
    private function exportFengDi($spreadsheet,$report_num_pre, $data_batch,$headerContent,$headerPageMargins) {
        $template_file = './storage/ExcelTemplate/山西封底.xls'; //考虑到兼容Linux，斜杠用“/”
        if(file_exists($template_file)) {
            $spreadsheet_common = IOFactory::load($template_file); //读取模板文件
            $worksheet_common = $spreadsheet_common->getSheet(0); //按索引获取表单，从 0 开始
            $worksheet_common->setTitle('注意事项'); //重命名工作表

            $worksheet_common->getHeaderFooter()->setOddHeader($headerContent);
            $worksheet_common->getHeaderFooter()->setEvenHeader($headerContent);
            $worksheet_common->getPageMargins()->setHeader($headerPageMargins); //设置眉头的页边距

            $spreadsheet->addExternalSheet($worksheet_common); //添加外部工作表
            //为了防止内存泄漏，建议用完手动清理
            $spreadsheet_common->disconnectWorksheets();
            unset($spreadsheet_common);
        }
    }

    /* 结论 */
    private function exportJieLun($spreadsheet, $contract_id, $DY_RiQi, $data_batch,$headerContent,$headerPageMargins) {
        $template_file = './storage/ExcelTemplate/山西结论.xls'; //考虑到兼容Linux，斜杠用“/”
        if(file_exists($template_file)) {
            $spreadsheet_common = IOFactory::load($template_file); //读取模板文件
            $worksheet_common = $spreadsheet_common->getSheet(0); //按索引获取表单，从 0 开始
            $worksheet_common->setTitle('检测说明'); //重命名工作表

            $worksheet_common->getHeaderFooter()->setOddHeader($headerContent);
            $worksheet_common->getHeaderFooter()->setEvenHeader($headerContent);
            $worksheet_common->getPageMargins()->setHeader($headerPageMargins); //设置眉头的页边距

            //查询出当前批次下 标准、工器具的集合，并去重
            $totalsql = "SELECT DISTINCT A.std_id,A.tool_name,A.tool_id,A.unit FROM a_contract_gaiyao AS A WHERE A.contract_id = :contract_id";
            $data_std_tool = DB::select($totalsql, ['contract_id' => $contract_id]);
            $this->total_page = $totalpage = count($data_std_tool)+2;

            $worksheet_common->getHeaderFooter()->setOddFooter(self::$footerContent); //页脚  Odd
            $worksheet_common->getHeaderFooter()->setEvenFooter(self::$footerContent); //页脚  Even
            $worksheet_common->getPageMargins()->setFooter(0.3); //设置页脚的页边距
            $worksheet_common->getPageMargins()->setBottom(0.5); //设置下边的页边距

            $totalCount = DB::table('a_contract_gaiyao')->where('contract_id', $contract_id)->sum('count'); //样品总量
            $worksheet_common->getCell('H2')->setValue($data_batch->entrusted_company); //客户名称
            $worksheet_common->getCell('AO8')->setValue("{$totalCount} 件"); //本批次中样品总量
            $worksheet_common->getCell('H4')->setValue(date("Y年m月d日", $DY_RiQi)); //到样日期

            //获取检测报告中的检测公司名称
            $company_name = DB::table('setting')->where('id', '1')->value('name_rep');
            $worksheet_common->getCell('H5')->setValue($company_name);
            //------------------------------------------检测日期 start ---------
            //检验日期开始时间
            $testdate_start = DB::table('a_sample_info')->where('contract_id', $contract_id)->min('test_time');
            if($testdate_start) {
                $testdate_start = strtotime($testdate_start); //将任何字符串的日期时间描述解析为 Unix 时间戳
                $testdate_start = date("Y年m月d日", $testdate_start);
            }
            //检验日期结束时间
            $testdate_end = DB::table('a_sample_info')->where('contract_id', $contract_id)->max('test_time');
            if($testdate_end) {
                $testdate_end = strtotime($testdate_end); //将任何字符串的日期时间描述解析为 Unix 时间戳
                $testdate_end = date("Y年m月d日", $testdate_end);
            }
            if($testdate_start === $testdate_end) {
                # 开始日期与结束日期相同
                $test_date = $testdate_start;
            }
            else {
                # code...
                $test_date = "{$testdate_start}～{$testdate_end}";
                // if ($info_pos->test_date !== '') {
                // $worksheet->getStyle($info_pos->test_date)->getFont()->setSize(9); //检测日期，字号设为 9
                // }
            }
            $worksheet_common->getCell('AG4')->setValue($test_date); //检测日期
            //----------------------------------------检测日期 end ---------------------------
            //检定证书日期
            $year = date('Y年',time());
            $worksheet_common->getCell('AO11')->setValue($year.'12月31日');
            $worksheet_common->getCell('AO12')->setValue($year.'12月31日');
            $worksheet_common->getCell('AO13')->setValue($year.'12月31日');
            $worksheet_common->getCell('AO14')->setValue($year.'12月31日');
            $worksheet_common->getCell('AO15')->setValue($year.'12月31日');
            $worksheet_common->getCell('AO16')->setValue($year.'12月31日');
            //----------------------------------------检测依据 start ---------------------------
            $sql = "SELECT
            a_standard.num,
            a_standard.`name`
            FROM
            a_standard
            WHERE
            a_standard.id IN ((
                SELECT DISTINCT
                a_contract_gaiyao.std_id
                FROM
                a_contract_gaiyao
                WHERE
                a_contract_gaiyao.contract_id = :contract_id
            ))";
            $standards = DB::select($sql, ['contract_id' => $contract_id]);
            $standard_arr = [];
            foreach($standards as $v) {
                # code...
                $standard_arr[] = "{$v->num} {$v->name}";
            }
            $standard_str = implode("\n", $standard_arr);
            $worksheet_common->getCell('I9')->setValue($standard_str); //检测依据
            //----------------------------------------检测依据 end ---------------------------

            //----------------------------------------检测结论 start ---------------------------
            $no_pass = DB::table('a_sample_info')->where('contract_id', $contract_id)->where('res', '<>', '1')->orderBy('bar_code', 'asc')->pluck('bar_code'); //查出不合格的样品
            $no_pass = $no_pass->toArray();

            $res_head = "　　根据检测规范，对{$data_batch->entrusted_company}委托的35kV及以下不停电作业工器具进行了检测，";
            $res_body = "";

            // $res_head="根据检测规范，对{$data_batch->entrusted_company}委托的10kV不停电作业工器具进行了检测，
            // 除编号200006006、200006007、200006010、200006016、200006017、200006091、200006092、200006093共计16件不合格，
            // 其余试品所检测的项目符合标准要求。";
            $no_pass_count = count($no_pass);
            if($no_pass_count === 0) {
                # 全部合格
                $res_foot = "所有试品所检测的项目符合标准要求。";
            }
            else if($no_pass_count === $totalCount) {
                # 全部不合格
                $res_foot = "所有试品全部不合格。";
            }
            else {
                # 部分不合格
                $no_pass_str = implode("、", $no_pass);
                $res_body = "除编号 {$no_pass_str} 共计 {$no_pass_count} 件不合格，";
                $res_foot = "其余试品所检测的项目符合标准要求。";
            }

            $worksheet_common->getCell('H17')->setValue("{$res_head}{$res_body}{$res_foot}"); //检测结论
            //----------------------------------------检测结论 end ---------------------------
            //获取温湿度
            $info = DB::table('a_contract_gaiyao')->select('std_id','tool_id')->where('contract_id', $contract_id)->get()->toArray();
            $temp_hum_pressure_data = DB::table('a_item_data as B')->select('temp', 'hum', 'pressure')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('item_id', '!=', 61)->where('std_id', $info[0]->std_id)->where('tool_id', $info[0]->tool_id)->where('res', '<', 2)->get();
            $se = self::tempHumPressureSE($temp_hum_pressure_data);
            [$temp, $hum, $pressure] = $se;
            $worksheet_common->getCell('W6')->setValue($temp); //检测结论
            $worksheet_common->getCell('AP6')->setValue($hum); //检测结论

            //----------------------------------------检测人员start------------------------------------
            $tester_names = DB::table('a_sample_info')->where('contract_id', $contract_id)->value('tester_names');
            $tester_names = str_replace('，','  ',$tester_names);
            $worksheet_common->getCell('H19')->setValue('  '.$tester_names); //检测结论
            //----------------------------------------检测人员end------------------------------------

            //----------------------------------------签字------------------------------------
            $contract = DB::table('a_contract')->find($contract_id);
            //批准签字
            if (file_exists(substr($contract->sign_14_url, 1))) {
                $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing->setPath('.' . $contract->sign_14_url);//图片的路径
                $drawing->setResizeProportional(true);          //true 是保持宽高比
                $drawing->setHeight(70);                        //高度
                $drawing->setOffsetX(5);
                $drawing->setCoordinates('AI17');//指定位置
                $drawing->setWorksheet($worksheet_common);
            }
            //审核签字
            if (file_exists(substr($contract->sign_11_url, 1))) {
                $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing->setPath('.' . $contract->sign_11_url);//图片的路径
                $drawing->setResizeProportional(true);          //true 是保持宽高比
                $drawing->setHeight(70);                        //高度
                $drawing->setOffsetX(5);
                $drawing->setCoordinates('AI21');//指定位置
                $drawing->setWorksheet($worksheet_common);
            }
            //----------------------------------------签字end------------------------------------

            $spreadsheet->addExternalSheet($worksheet_common); //添加外部工作表
            //为了防止内存泄漏，建议用完手动清理
            $spreadsheet_common->disconnectWorksheets();
            unset($spreadsheet_common);
        }
    }

    /* 封面 */
    private function exportFengMian($report_num_pre, $data_batch,$headerContent,$headerPageMargins) {
        $template_file = './storage/ExcelTemplate/山西封面.xls'; //考虑到兼容Linux，斜杠用“/”
        if(!file_exists($template_file)) {
            die('封面模板文件不存在');
        }
        $spreadsheet = IOFactory::load($template_file); //读取模板文件
        $worksheet = $spreadsheet->getSheet(0); //按索引获取表单，从 0 开始
        $worksheet->setTitle('封面'); //重命名工作表

        $worksheet->getHeaderFooter()->setOddHeader($headerContent);
        $worksheet->getHeaderFooter()->setEvenHeader($headerContent);
        $worksheet->getPageMargins()->setHeader($headerPageMargins); //设置眉头的页边距

        //https://cloud.tencent.com/developer/ask/sof/106487395 --- 如何将部分文本设置为粗体
        $richText = new \PhpOffice\PhpSpreadsheet\RichText\RichText();
        $payable = $richText->createTextRun('报告编号：');
        $payable->getFont()->setBold(true);
        $richText->createText($report_num_pre.$data_batch->batchNum);
        $worksheet->getCell('AM2')->setValue($richText); //报告编号前缀 + 报告编号
        $worksheet->getCell('O14')->setValue($data_batch->entrusted_company); //委托单位
        //$worksheet->getCell('T24')->setValue($data_batch->client_name); //送检单位
        // $worksheet->getCell('P26')->setValue($data_batch->client_addr); //地址
        //$worksheet->getCell('T27')->setValue($data_batch->test_kind); //检测性质
        $worksheet->getCell('A49')->setValue(date("Y 年 m 月 d 日")); //日期

        return $spreadsheet;
    }

    /* 工器具试验内容页 */
    private function exportContent($spreadsheet, $contract_id, $data_batch, $headerContent, $headerPageMargins) {
        $sql = "SELECT DISTINCT
        A.std_id,
        A.tool_name,
        A.tool_id,
        A.unit
        FROM
        a_contract_gaiyao AS A
        WHERE
        A.contract_id = :contract_id";
        //查询出当前批次下 标准、工器具的集合，并去重
        $data_std_tool = DB::select($sql, ['contract_id' => $contract_id]);

        foreach($data_std_tool as $k => $v) {
            # 检测模版文件是否存在
            $template_file = "./storage/ExcelTemplate/{$v->std_id}/{$v->tool_name}.xls"; //考虑到兼容Linux，斜杠用“/”
            if(!file_exists($template_file)) {
                //为了防止内存泄漏，建议用完手动清理
                $spreadsheet->disconnectWorksheets();
                unset($spreadsheet);
                die("{$v->tool_name}的模板文件不存在");
            }

            # 查询数据的位置信息，即数据要存到Excel的哪个单元格
            $info_pos = DB::table('a_excel_pos as B')
            ->leftJoin('a_std_tool_rel as A', 'A.id', '=', 'B.std_tool_rel_id')
            ->where('std_id', $v->std_id)
            ->where('tool_id', $v->tool_id)
            ->first();

            if(!$info_pos) {
                //为了防止内存泄漏，建议用完手动清理
                $spreadsheet->disconnectWorksheets();
                unset($spreadsheet);
                die("{$v->tool_name} 未指定数据在模板文件中的位置");
            }
            // if ($v->tool_name == '携带型短路接地线' && isAdmin()) {
            //     // code...
            //     // dd($info_pos);
            // }

            //未指定行号
            if($info_pos->line_num === '' || intval($info_pos->line_num) < 1) {
                //为了防止内存泄漏，建议用完手动清理
                $spreadsheet->disconnectWorksheets();
                unset($spreadsheet);
                die("{$v->tool_name} 未指定数据在模板文件中的行号");
            }
            //==========================去具体工器具模版文件 复制表单 到 封面模板文件========================
            $spreadsheet2 = IOFactory::load($template_file); //读取被复制的模板文件
            // $worksheet2 = $spreadsheet2->getSheet(0); //按索引获取被复制的表单，从 0 开始
            $worksheet3 = $spreadsheet2->getActiveSheet(); //在MS Excel（或其他适当的Spreadsheet程序）中打开工作簿时将处于活动状态的工作表

            $worksheet3->setTitle("HelloWorld-gww");
            $worksheet2 = clone $worksheet3; //按索引获取被复制的表单，从 0 开始
            $spreadsheet->addExternalSheet($worksheet2); //添加外部工作表
            // $objClonedWorksheet = clone $objPHPExcel1->getSheetByName('Worksheet 1');
            // $objPHPExcel->addExternalSheet($objClonedWorksheet);

            //getSheetCount()方法将告诉您工作簿中的工作表数量,class Spreadsheet 的方法
            //为了防止内存泄漏，建议用完手动清理
            $spreadsheet2->disconnectWorksheets();
            unset($spreadsheet2);
            //=========================================================================================
            $worksheet = $spreadsheet->getSheetByName("HelloWorld-gww"); //按表单名 获取到激活的表单
            $worksheet->setTitle("{$v->tool_name}{$v->std_id}");
            // $worksheet->getPageSetup()->setPrintArea('A1:BA1000');//指定打印区域

            /* if ($info_pos->company !== '') {
    $company_name = DB::table('setting')->where('id', '1')->value('name_rep');//获取检测公司名称
    $worksheet->getCell($info_pos->company)->setValue($company_name); //检测公司名称
}
if ($info_pos->report_num !== '') {
    $worksheet->getCell($info_pos->report_num)->setValue($report_num_pre . $data_batch->batchNum); //报告编号前缀 + 报告编号
} */
            // if(!$worksheet) dd($worksheet);//---------------------------------------------------------------------------------------------------------------------------????
            // if(!$worksheet) dd("{$v->tool_name}{$v->std_id}");//-------------------------------------------------------------------------------------------------------??????
            $worksheet->getHeaderFooter()->setOddHeader($headerContent); //检测公司名称+报告编号前缀+报告编号 Odd
            $worksheet->getHeaderFooter()->setEvenHeader($headerContent); //检测公司名称+报告编号前缀+报告编号 Even
            $worksheet->getPageMargins()->setHeader($headerPageMargins); //设置眉头的页边距

            $currpage = $k+2;
            $sample_totalpage = $this->total_page;

            $worksheet->getHeaderFooter()->setOddFooter(self::$footerContent); //页脚  Odd
            $worksheet->getHeaderFooter()->setEvenFooter(self::$footerContent); //页脚  Even
            $worksheet->getPageMargins()->setFooter(0.3); //设置页脚的页边距
            $worksheet->getPageMargins()->setBottom(0.5); //设置下边的页边距

            $DY_RiQi = strtotime($data_batch->created_at); //将表示时间和日期的字符串转化为相应的时间戳
            if($info_pos->come_date !== '') {
                $worksheet->getCell($info_pos->come_date)->setValue(date("Y年m月d日", $DY_RiQi)); //到样日期
            }
            //------------------------------------------检测日期---------
            //检验日期开始时间
            $testdate_start = DB::table('a_sample_info as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->min('test_time');
            if($testdate_start) {
                $testdate_start = strtotime($testdate_start); //将任何字符串的日期时间描述解析为 Unix 时间戳
                $testdate_start = date("Y年m月d日", $testdate_start);
            }
            //检验日期结束时间
            $testdate_end = DB::table('a_sample_info as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->max('test_time');
            if($testdate_end) {
                $testdate_end = strtotime($testdate_end); //将任何字符串的日期时间描述解析为 Unix 时间戳
                $testdate_end = date("Y年m月d日", $testdate_end);
            }
            if($testdate_start === $testdate_end) {
                # 开始日期与结束日期相同
                $test_date = $testdate_start;
            }
            else {
                # code...
                $test_date = "{$testdate_start}～{$testdate_end}";
                // if ($info_pos->test_date !== '') {
                // $worksheet->getStyle($info_pos->test_date)->getFont()->setSize(9); //检测日期，字号设为 9
                // }
            }
            if($info_pos->test_date !== '') {
                $worksheet->getCell($info_pos->test_date)->setValue($test_date); //检测日期
            }
            //-------------------------------------------------------------------
            $sample_count = DB::table('a_contract_gaiyao')->where('contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->sum('count');
            if($info_pos->sample_count !== '') {
                $worksheet->getCell($info_pos->sample_count)->setValue($sample_count.$v->unit); //样品数量
            }
            $temp_hum_pressure_data = DB::table('a_item_data as B')->select('temp', 'hum', 'pressure')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('item_id', '!=', 61)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->where('res', '<', 2)->get();
            $se = ExportReportPublicController::tempHumPressureSE($temp_hum_pressure_data);
            list($temp, $hum, $pressure) = $se;
            //------------------------------------------ 温度 start---------
            // $temp_start = DB::table('a_item_data as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->min(DB::raw('temp*1'));
            // $temp_end = DB::table('a_item_data as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->max(DB::raw('temp*1'));
            if($info_pos->temp !== '') {
                $worksheet->getCell($info_pos->temp)->setValue($temp); //温度
            }
            //------------------------------------------ 温度 end ---------
            //------------------------------------------ 相对湿度 start---------
            // $hum_start = DB::table('a_item_data as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->min(DB::raw('hum*1'));
            // $hum_end = DB::table('a_item_data as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->max(DB::raw('hum*1'));
            if($info_pos->hum !== '') {
                $worksheet->getCell($info_pos->hum)->setValue($hum); //相对湿度
            }
            //------------------------------------------ 相对湿度 end ---------
            //------------------------------------------ 大气压力 start---------
            // $pressure_start = DB::table('a_item_data as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->min(DB::raw('pressure*1'));
            // $pressure_end = DB::table('a_item_data as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->max(DB::raw('pressure*1'));
            if($info_pos->pressure !== '') {
                $worksheet->getCell($info_pos->pressure)->setValue($pressure); //大气压力
            }
            //------------------------------------------ 大气压力 end ---------
            //------------------------------------------ 天气 start---------
            $wea = DB::table('a_item_data as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->where('wea', '!=', '')->value('wea');
            if($info_pos->wea !== '') {
                if($wea) {
                    $worksheet->getCell($info_pos->wea)->setValue($wea);
                } //天气
            }
            //------------------------------------------ 天气 end ---------
            $sample_infos = DB::table('a_sample_info as B')
            ->select('B.*', 'A.tool_name', 'A.voltage', 'A.class', 'A.std_id', 'A.tool_id', 'A.item_ids', 'A.vol_id', 'class_id')
            ->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')
            ->where('B.contract_id', $contract_id)
            ->where('std_id', $v->std_id)
            ->where('tool_id', $v->tool_id)
            ->get();
            $rowIndex = $info_pos->line_num;

            if($v->tool_name === '绝缘斗臂车') {
                # 内容页 --行，斗臂车
                // 隐藏多余的行
                $sample_count = $sample_infos->count();
                $listRowNum = 9;  //每个样品占了9行数据
                //插入行和内容
                $insertRows = ($sample_count - 1)*$listRowNum;
                if($insertRows > 0){
                    $worksheet->insertNewRowBefore($rowIndex, $insertRows); //在第x行之前插入9个新行
                }
                $max = $insertRows + $rowIndex;
                $initData = [];
                for ($m=0; $m < $listRowNum; $m++) {
                    $cell = 'X'.($max+$m);
                    $initData[$cell] = $worksheet->getCell($cell)->getValue();
                }
                // dd($initData);
                for($i = $rowIndex; $i < $max; $i += $listRowNum) {
                    $listMaxRowIndex = $i + $listRowNum - 1;
                    $worksheet->mergeCells('A'.$i.':D'.$listMaxRowIndex)->setCellValue('A'.$i, '—');
                    $worksheet->mergeCells('E'.$i.':P'.$listMaxRowIndex)->setCellValue('E'.$i, '—');
                    $worksheet->mergeCells('Q'.$i.':W'.$listMaxRowIndex)->setCellValue('Q'.$i, '—');
                    for($j = $i; $j <= $listMaxRowIndex; $j ++) {
                        // dump((string)($max + $j%$listRowNum));
                        $worksheet->mergeCells('X'.$j.':AR'.$j)->setCellValue('X'.$j, $initData['X'.($max + $j%$listRowNum)]);
                        $worksheet->mergeCells('AS'.$j.':AY'.$j)->setCellValue('AS'.$j, '—');
                        $worksheet->mergeCells('AZ'.$j.':BG'.$j)->setCellValue('AZ'.$j, '—');
                        $worksheet->mergeCells('BH'.$j.':BN'.$j)->setCellValue('BH'.$j, '—');
                        $worksheet->mergeCells('BO'.$j.':BU'.$j)->setCellValue('BO'.$j, '—');
                        $worksheet->mergeCells('BV'.$j.':CC'.$j)->setCellValue('BV'.$j, '—');
                    }
                    $worksheet->mergeCells('CD'.$i.':CI'.$listMaxRowIndex)->setCellValue('CD'.$i, '—');
                    $worksheet->mergeCells('CJ'.$i.':CU'.$listMaxRowIndex)->setCellValue('CJ'.$i, '—');
                }
                $this->exportLineContentDouBiChe($sample_infos, $worksheet, $info_pos);
                //插入分页空行
                if($sample_count > 1) {
                    $perCount = 3; //每页斗臂车数量
                    $beColumn = $rowIndex;
                    for($i = 1; $i <= $sample_count; $i ++) {
                        if($i === 1) {
                            $insetNum = 1;
                            $beColumn += $listRowNum;
                        }
                        elseif(($i - 1)%$perCount === 0) {
                            $insetNum = 1;
                            $page = ($i - 1)/$perCount - 1;
                            $addNums = $page === 0 ? 1 : $page*$insetNum;
                            $beColumn += $perCount*$listRowNum + $addNums;
                            if($beColumn > $max) {
                                $insetNum = 0;
                            }
                        }
                        else {
                            $insetNum = 0;
                        }
                        if($insetNum > 0) {
                            $worksheet->insertNewRowBefore($beColumn, $insetNum);
                            $styleArray = [
                                'borders' => [
                                    'allBorders' => [
                                        'borderStyle' => Border::BORDER_NONE,
                                    ],
                                ]
                            ];
                            $worksheet->getStyle('A'.$beColumn.':CU'.($beColumn + $insetNum - 1))->applyFromArray($styleArray);
                        }
                    }
                }
            }
            else {
                # 内容页 --行，非斗臂车
                $this->exportLineContentOther($sample_infos, $worksheet, $rowIndex, $info_pos);
            }
        } //最外层 foreach 结尾
    } //方法


    /* 内容页 --行，非斗臂车*/
    private function exportLineContentOther($sample_infos, $worksheet, $rowIndex, $info_pos) {
        $sampleCount = $sample_infos->count();
        $insertRow = $sampleCount - 1;
        if($insertRow > 0){
            $worksheet->insertNewRowBefore($rowIndex, $insertRow); //插入新行
            $highestColumn = $worksheet->getHighestDataColumn();
            $lastIndex = $sampleCount-1+$rowIndex;
            $rowheight = $worksheet->getRowDimension($lastIndex)->getRowHeight();
            for($i = $rowIndex; $i < $lastIndex; $i++){
                $worksheet->getRowDimension($i)->setRowHeight($rowheight);
            }
        }
        foreach($sample_infos as $kk => $vv) {
            $this->mergeColCells($worksheet, $info_pos, $rowIndex);
            if($info_pos->serial_num1 !== '') {
                 $worksheet->getCell($info_pos->serial_num1 . $rowIndex)->setValue("" . ($kk + 1)); //序号
             }
             if($info_pos->sample_num1 !== '') {
                 $worksheet->getCell($info_pos->sample_num1 . $rowIndex)->setValueExplicit($vv->bar_code, DataType::TYPE_STRING2); //样品号 9位
             }
             if($info_pos->self_num1 !== '') {
                $worksheet->getCell($info_pos->self_num1.$rowIndex)->setValueExplicit($vv->self_num, DataType::TYPE_STRING2); //自编号
            }
            if($info_pos->voltage1 !== '') {
                $voltageWithoutKV = str_replace("kV", "", $vv->voltage); //将额定电压中的 kV去掉
                $worksheet->getCell($info_pos->voltage1.$rowIndex)->setValue($voltageWithoutKV); //额定电压
            }
            if($info_pos->class1 !== '') {
                $classWithoutKN = str_replace("kN", "", $vv->class); //将额定电压中的 kV去掉
                $worksheet->getCell($info_pos->class1.$rowIndex)->setValue($classWithoutKN); //分类
            }
            if($info_pos->res1 !== '') {
                if($vv->res === 1) {
                    $panding = '合格';
                }
                elseif($vv->res === 2) {
                    $panding = '不合格';
                }
                else {
                    # code...
                    $panding = '';
                }
                $worksheet->getCell($info_pos->res1.$rowIndex)->setValue($panding); //判定
            }
            //有效日期有位置 而且 周期不为0或null
            if($info_pos->valid_date1 !== '' && $info_pos->cycle) {
                if($vv->res === 1) {
                    //合格
                    $test_time = $vv->test_time; //试验日期
                    $cycle = ($info_pos->cycle)*12; //周期

                    $valid_date = date("Y-m-d", strtotime("+$cycle month", strtotime($test_time)));
                    $first_date = date("Y-m-d", strtotime("first day of +$cycle month", strtotime($test_time)));
                    if(substr($valid_date, 0, 7) !== substr($first_date, 0, 7)) {
                        $valid_date = date("Y-m-d", strtotime("last day of +$cycle month", strtotime($test_time)));
                    }

                    $valid_date = date('Y-m-d', strtotime("-1 day", strtotime($valid_date)));
                }
                else {
                    # 不合格
                    $valid_date = "—";
                }

                $worksheet->getCell($info_pos->valid_date1.$rowIndex)->setValue($valid_date); //有效日期
            }

            //------------------------------------------ 试验条件 start ---------
            $key_condition = DB::table('a_condition_key')->select('id', 'item_id', 'pos_start', 'pos_end')->where([
                ['std_id', '=', $vv->std_id],
                ['tool_id', '=', $vv->tool_id],
                ['pos_start', '!=', ''],
                ['pos_end', '!=', '']
            ])->get();

            foreach($key_condition as $vvv) {
                $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
                $b = in_array($vvv->item_id, $arr_item_id); //in_array($value, $array);
                if($b) {
                    # code...
                    $val_condition = DB::table('a_condition')->where('condition_key_id', '=', $vvv->id)->whereIn('vol_class_id', [0, $vv->vol_id, $vv->class_id])->value('val');
                }
                else {
                    # code...
                    $val_condition = '/';
                }
                $worksheet->mergeCells($vvv->pos_start.$rowIndex.':'.$vvv->pos_end.$rowIndex); //试验条件  合并单元格
                $worksheet->getCell($vvv->pos_start.$rowIndex)->setValue($val_condition); //试验条件
            } //内内层 foreach 结尾
            //------------------------------------------ 试验条件 end ---------

            //------------------------------------------ 单项目的检测结果（符合、不符合） start ---------
            $item_res = DB::table('a_std_tool_item_rel')->select('id', 'testitem_id', 'pos_start', 'pos_end')->where([
                ['std_id', '=', $vv->std_id],
                ['tool_id', '=', $vv->tool_id],
                ['pos_start', '!=', ''],
                ['pos_end', '!=', '']
            ])->get();

            foreach($item_res as $vvv) {
                $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
                $b = in_array($vvv->testitem_id, $arr_item_id); //in_array($value, $array);
                if($b) {
                    # code...
                    $test_res = DB::table('a_item_data')->where([
                        ['sample_info_id', '=', $vv->id],
                        ['item_id', '=', $vvv->testitem_id]
                    ])->value('res');
                    //结论,0-不符合，1-符合，2-未录入
                    if($test_res === 0) {
                        $test_res = '不符合';
                    }
                    elseif($test_res === 1) {
                        $test_res = '符合';
                    }
                    else {
                        # code...
                        $test_res = '/';
                    }
                }
                else {
                    # code...
                    $test_res = '/';
                }
                $worksheet->mergeCells($vvv->pos_start.$rowIndex.':'.$vvv->pos_end.$rowIndex); //单项目的检测结果  合并单元格
                $worksheet->getCell($vvv->pos_start.$rowIndex)->setValue($test_res); //单项目的检测结果
            } //内内层 foreach 结尾
            //------------------------------------------ 单项目的检测结果（符合、不符合） end   ---------

            //------------------------------------------ 试验数据 start ---------
            $key_data = DB::table('a_testdata_key')->select('id', 'item_id', 'pos_start', 'pos_end')->where([
                ['std_id', '=', $vv->std_id],
                ['tool_id', '=', $vv->tool_id],
                ['pos_start', '!=', ''],
                ['pos_end', '!=', '']
            ])->get();

            foreach($key_data as $vvv) {
                $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
                $b = in_array($vvv->item_id, $arr_item_id); //in_array($value, $array);
                if($b) {
                    # code...
                    $val_testdata = DB::table('a_input_data')->where([
                        ['sample_info_id', '=', $vv->id],
                        ['testdata_key_id', '=', $vvv->id]
                    ])->value('val');
                    // dd( "{$val_testdata} --- {$vv->id}---- {$vvv->id}");
                }
                else {
                    # code...
                    $val_testdata = '/';
                }
                $worksheet->mergeCells($vvv->pos_start.$rowIndex.':'.$vvv->pos_end.$rowIndex); //试验数据  合并单元格
                $worksheet->getCell($vvv->pos_start.$rowIndex)->setValueExplicit($val_testdata, DataType::TYPE_STRING2); //试验数据
            } //内内层 foreach 结尾
            //------------------------------------------ 试验数据 end ---------
            $rowIndex ++;
        }
    } //方法

    /* 内容页 --行，斗臂车 */
    private function exportLineContentDouBiChe($sample_infos, $worksheet, $info_pos) {
        $rowNumInit = $info_pos->line_num;
        foreach($sample_infos as $kk => $vv) {

            $rowNum = $rowNumInit + $kk*9; //每个样品占据9行，此为每个样品第一行的行号
            //单独设置这几行的高度
            if($kk == 0){
                for($iii = 0; $iii <9; $iii++){
                    $first_yp =  $iii+$rowNum;
                    $worksheet->getRowDimension($first_yp)->setRowHeight(30);
                }
            }
            $worksheet->getCell("A{$rowNum}")->setValue($kk + 1); //序号
            $worksheet->getCell("E{$rowNum}")->setValueExplicit($vv->bar_code, DataType::TYPE_STRING2); //样品号 9位
            if(true) {
                $voltageWithoutKV = str_replace("kV", "", $vv->voltage); //将额定电压中的 kV去掉
                $worksheet->getCell("Q{$rowNum}")->setValue($voltageWithoutKV); //额定电压
            }

            if(true) {
                if($vv->res === 1) {
                    $panDing = '合格';
                }
                elseif($vv->res === 2) {
                    $panDing = '不合格';
                }
                else {
                    # code...
                    $panDing = '';
                }
                $worksheet->getCell("CD{$rowNum}")->setValue($panDing); //判定
            }

            //周期不为0或null
            if($info_pos->cycle) {
                if($vv->res === 1) {
                    //合格
                    $test_time = $vv->test_time; //试验日期
                    $cycle = ($info_pos->cycle)*12; //周期

                    $valid_date = date("Y-m-d", strtotime("+$cycle month", strtotime($test_time)));
                    $first_date = date("Y-m-d", strtotime("first day of +$cycle month", strtotime($test_time)));
                    if(substr($valid_date, 0, 7) !== substr($first_date, 0, 7)) {
                        $valid_date = date("Y-m-d", strtotime("last day of +$cycle month", strtotime($test_time)));
                    }

                    $valid_date = date('Y-m-d', strtotime("-1 day", strtotime($valid_date)));
                }
                else {
                    # 不合格
                    $valid_date = "—";
                }

                $worksheet->getCell("CJ{$rowNum}")->setValue($valid_date); //有效日期
            }

            //------------------------------------------ 单项目的检测结果（符合、不符合） start ---------
            $item_res = DB::table('a_std_tool_item_rel')->select('id', 'testitem_id', 'pos_start', 'pos_end')->where([
                ['std_id', '=', $vv->std_id],
                ['tool_id', '=', $vv->tool_id],
                ['pos_start', '!=', ''],
                ['pos_end', '!=', '']
            ])->get();

            $arr_line = [];
            $arr_line[61] = $rowNum + 0; //外观检查
            $arr_line[44] = $rowNum + 1; //绝缘臂（整车）泄漏
            $arr_line[20] = $rowNum + 1; //绝缘臂（整车）泄漏
            $arr_line[45] = $rowNum + 2; //绝缘臂（整车）耐压
            $arr_line[21] = $rowNum + 2; //绝缘臂（整车）耐压
            $arr_line[22] = $rowNum + 3; //下臂 耐压
            $arr_line[41] = $rowNum + 4; //内斗层向 耐压
            $arr_line[40] = $rowNum + 5; //外斗表面 泄漏
            $arr_line[39] = $rowNum + 6; //外斗表面 耐压
            $arr_line[42] = $rowNum + 7; //吊臂 耐压
            $arr_line[55] = $rowNum + 8; //额定荷载全工况

            foreach($item_res as $vvv) {
                $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
                $b = in_array($vvv->testitem_id, $arr_item_id); //in_array($value, $array);
                if($b) {
                    # code...
                    $test_res = DB::table('a_item_data')->where([
                        ['sample_info_id', '=', $vv->id],
                        ['item_id', '=', $vvv->testitem_id]
                    ])->value('res');
                    //结论,0-不符合，1-符合，2-未录入
                    if($test_res === 0) {
                        $test_res = '不符合';
                    }
                    elseif($test_res === 1) {
                        $test_res = '符合';
                    }
                    else {
                        # code...
                        $test_res = '/';
                    }
                }
                else {
                    # code...
                    $test_res = '/';
                }
                // $worksheet->mergeCells($vvv->pos_start . $rowIndex . ':' . $vvv->pos_end . $rowIndex); //单项目的检测结果  合并单元格


                $worksheet->getCell("{$vvv->pos_start}{$arr_line[$vvv->testitem_id]}")->setValue($test_res); //单项目的检测结果
            } //内内层 foreach 结尾
            //------------------------------------------ 单项目的检测结果（符合、不符合） end   ---------

            //------------------------------------------ 试验数据 start ---------
            $key_data = DB::table('a_testdata_key')->select('id', 'item_id', 'pos_start', 'pos_end')->where([
                ['std_id', '=', $vv->std_id],
                ['tool_id', '=', $vv->tool_id],
                ['pos_start', '!=', ''],
                ['pos_end', '!=', '']
            ])->get();

            $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
            foreach($key_data as $vvv) {
                $b = in_array($vvv->item_id, $arr_item_id); //in_array($value, $array);
                if($b) {
                    # code...
                    $val_testdata = DB::table('a_input_data')->where([
                        ['sample_info_id', '=', $vv->id],
                        ['testdata_key_id', '=', $vvv->id]
                    ])->value('val');
                    // dd( "{$val_testdata} --- {$vv->id}---- {$vvv->id}");
                }
                else {
                    # code...
                    $val_testdata = '/';
                }
                // $worksheet->mergeCells($vvv->pos_start . $rowIndex . ':' . $vvv->pos_end . $rowIndex); //试验数据  合并单元格
                $worksheet->getCell("{$vvv->pos_start}{$arr_line[$vvv->item_id]}")->setValueExplicit($val_testdata, DataType::TYPE_STRING2); //试验数据
            } //内内层 foreach 结尾
            //------------------------------------------ 试验数据 end ---------
            // $rowIndex++;
        }
    } //方法

    //合并指定单元格
    private function mergeColCells($worksheet, $info_pos, $rowIndex) {
        // $worksheet->mergeCells('A21:E21');//合并单元格
        if($info_pos->serial_num1 !== '' && $info_pos->serial_num2 !== '') {
            $worksheet->mergeCells($info_pos->serial_num1.$rowIndex.':'.$info_pos->serial_num2.$rowIndex); //序号  合并单元格
        }
        if($info_pos->sample_num1 !== '' && $info_pos->sample_num2 !== '') {
            $worksheet->mergeCells($info_pos->sample_num1.$rowIndex.':'.$info_pos->sample_num2.$rowIndex); //样品号  合并单元格
        }
        if($info_pos->self_num1 !== '' && $info_pos->self_num2 !== '') {
            $worksheet->mergeCells($info_pos->self_num1.$rowIndex.':'.$info_pos->self_num2.$rowIndex); //自编号  合并单元格
        }
        if($info_pos->voltage1 !== '' && $info_pos->voltage2 !== '') {
            $worksheet->mergeCells($info_pos->voltage1.$rowIndex.':'.$info_pos->voltage2.$rowIndex); //额定电压  合并单元格
        }
        if($info_pos->class1 !== '' && $info_pos->class2 !== '') {
            $worksheet->mergeCells($info_pos->class1.$rowIndex.':'.$info_pos->class2.$rowIndex); //分类  合并单元格
        }
        if($info_pos->res1 !== '' && $info_pos->res2 !== '') {
            $worksheet->mergeCells($info_pos->res1.$rowIndex.':'.$info_pos->res2.$rowIndex); //判定  合并单元格
        }
        if($info_pos->valid_date1 !== '' && $info_pos->valid_date2 !== '') {
            $worksheet->mergeCells($info_pos->valid_date1.$rowIndex.':'.$info_pos->valid_date2.$rowIndex); //有效日期  合并单元格
        }
    }

    //温湿度，大气压最大最小值
    private static function tempHumPressureSE($data,$unit=true) {
        if (is_object($data)) {
            $data = $data->toArray();
        }
        $columns = ['temp'=>1,'hum'=>1,'pressure'=>2];  //字段=》保留小数点位数
        $temp = $hum = $pressure = ''; //初始化
        foreach ($columns as $column => $float) {
            $$column = ary_filter(array_column($data,$column));
            $min = $$column ? min($$column) : '';
            $max = $$column ? max($$column) : '';
            $min = $min ? sprintf("%1\$.".$float."f",$min) : '';
            $max = $max ? sprintf("%1\$.".$float."f",$max) : '';
            unset($$column);
            $$column =  ($min !== '' && $max !== '' && $min != $max) ? "{$min}～{$max}" : ($max ? $max : '');
        }
        if ($unit) {
            return [$temp.'℃', $hum.'%', $pressure.'kPa'];
        }
        return [$temp, $hum, $pressure];
    }

}//类
